package com.wind.trademark.notice.dao.impl;

import java.util.Map;

import org.apache.commons.lang.StringUtils;

public class NoticeSql {

	public static String queryNoticeSQL(Map<String, Object> params) {
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("	SELECT  ");
		stringBuffer.append("		no_notice.*, tk_task.TASK_NAME,  ");
		stringBuffer.append("		create_user.`NAME` CREATE_NAME,  ");
		stringBuffer.append("		update_user.`NAME` LAST_UPDATE_NAME  ");
		stringBuffer.append("	FROM  ");
		stringBuffer.append("		no_notice no_notice  ");
		stringBuffer
				.append("	LEFT JOIN tk_task tk_task ON tk_task.ID = no_notice.TASK_ID,  ");
		stringBuffer.append("	 acc_user create_user,  ");
		stringBuffer.append("	 acc_user update_user  ");
		stringBuffer.append("	WHERE  ");
		stringBuffer.append("		1 = 1  ");
		stringBuffer.append("	AND no_notice.CREATE_BY = create_user.ID  ");
		stringBuffer.append("	AND no_notice.LAST_UPDATE_BY = update_user.ID  ");

		if (StringUtils.isNotEmpty((String) params.get("title"))) {
			stringBuffer.append("  AND no_notice.TITLE LIKE :title  ");
			params.put("title", "%"
					+ params.get("title").toString().toLowerCase() + "%");

		}
		if (StringUtils.isNotEmpty((String) params.get("taskId"))) {
			stringBuffer.append("  AND no_notice.TASK_ID =:taskId ");
		}
		if (StringUtils.isNotEmpty((String) params.get("status"))) {
			stringBuffer.append("  AND no_notice.status =:status ");
		}
		if (StringUtils.isNotEmpty((String) params.get("isAboutTask"))) {
			if ("Y".equals(params.get("isAboutTask"))) {
				stringBuffer.append("  AND tk_task.ID IS NOT NULL");
			} else if ("N".equals(params.get("isAboutTask"))) {
				stringBuffer.append("  AND tk_task.ID IS  NULL");
			}
		}
		if (StringUtils.isNotEmpty((String) params.get("executeTimeStartDate"))) {
			stringBuffer
					.append("AND no_notice.EXECUTE_TIME >=:executeTimeStartDate ");
		}
		if (StringUtils.isNotEmpty((String) params.get("executeTimeEndDate"))) {
			stringBuffer
					.append("AND no_notice.EXECUTE_TIME <=:executeTimeEndDate ");
		}
		stringBuffer.append("  AND no_notice.CREATE_BY =:createBy ");
		stringBuffer.append("	ORDER BY  ");
		stringBuffer.append("		no_notice.CREATE_TIME DESC ");
		return stringBuffer.toString();
	}

	public static String deleteNotices() {

		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("DELETE ");
		stringBuffer.append("FROM no_notice  ");
		stringBuffer.append("WHERE id IN (:ids) ");
		return stringBuffer.toString();

	}

	public static String deleteNoReceiveMailByNoticeId() {
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("DELETE ");
		stringBuffer.append("FROM no_receive_mail  ");
		stringBuffer.append("WHERE notice_id =:noticeId");
		return stringBuffer.toString();
	}

	public static String deleteNoReceiveMessageByNoticeId() {
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("DELETE ");
		stringBuffer.append("FROM no_receive_message  ");
		stringBuffer.append("WHERE notice_id =:noticeId");
		return stringBuffer.toString();
	}

	public static String deleteNoReceiveSystemByNoticeId() {
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("DELETE ");
		stringBuffer.append("FROM no_receive_system  ");
		stringBuffer.append("WHERE notice_id =:noticeId");
		return stringBuffer.toString();
	}

	public static String loadUserMobileList() {
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("   SELECT  ");
		stringBuffer.append("   	CONCAT(acc_user.`NAME`,':', acc_user.MOBILE ) name  ");
		stringBuffer.append("   FROM  ");
		stringBuffer.append("   	acc_user acc_user  ");
		stringBuffer.append("   WHERE  ");
		stringBuffer.append("   	acc_user.`ENABLE` = '1'  ");
		stringBuffer.append("   ORDER BY  ");
		stringBuffer.append("   	acc_user.`NAME` ");
		return stringBuffer.toString();
	}
	public static String loadUserEmailList() {
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("   SELECT  ");
		stringBuffer.append("   	CONCAT(acc_user.`NAME`,':', acc_user.EMAIL ) name  ");
		stringBuffer.append("   FROM  ");
		stringBuffer.append("   	acc_user acc_user  ");
		stringBuffer.append("   WHERE  ");
		stringBuffer.append("   	acc_user.`ENABLE` = '1'  ");
		stringBuffer.append("   ORDER BY  ");
		stringBuffer.append("   	acc_user.`NAME` ");
		return stringBuffer.toString();
	}

}
